import datetime
import calendar
import time

__all__ = ['Market']
from ..storage import *
class Market(Storage):

    TABLE_SELLERS = "sellers"
    TABLE_BUYERS  = "buyers"
    TABLES = [TABLE_SELLERS, TABLE_BUYERS]

    SQL_DROP = ["""
        DROP TABLE IF EXISTS "{{table}}"
    """]

    SQL_CREATE = ["""
        CREATE TABLE "{{table}}" (
            "order_id"          bigint          not null,
            "station_id"        integer         not null,
            "system_id"         integer         not null,
            "region_id"         integer         not null,
            "item_id"           integer         not null,
            "range"             smallint        not null,
            "bid"               boolean         not null, -- true for buy orders, false for sell orders
            "price"             float           not null,
            "remains"           integer         not null,
            "entered"           integer         not null,
            "minimal"           integer         not null,
            "duration"          integer         not null, -- seconds
            "issued"            integer         not null, -- unix timestamp
            "expires"           integer         not null, -- unix timestamp
            "exported"          integer         not null, -- unix timestamp
            "displaced"         integer         not null, -- !!! rename to REPLACED/expired/overrided/excluded/whatever or negatives: actual/fresh/etc
            "export_item"       varchar(200)    not null,--!!!!!!!!! TODO: устаревание по ним, а не по region_id & item_id. на случай пустого csv очень пригодится (инфа из имени файла)
            "export_region"     varchar(200)    not null,
--          FOREIGN KEY ("station_id") REFERENCES "stations" MATCH FULL ON DELETE NO ACTION ON UPDATE NO ACTION,
            FOREIGN KEY ("system_id" ) REFERENCES "systems"  MATCH FULL ON DELETE NO ACTION ON UPDATE NO ACTION,
            FOREIGN KEY ("region_id" ) REFERENCES "regions"  MATCH FULL ON DELETE NO ACTION ON UPDATE NO ACTION,
            FOREIGN KEY ("item_id"   ) REFERENCES "items"    MATCH FULL ON DELETE NO ACTION ON UPDATE NO ACTION,
            PRIMARY KEY ("order_id", "exported")
        ) WITHOUT OIDS """,

        #@TODO: determine which indexes are useful in searches; then which are useless, and drop(delete) them
        """ CREATE INDEX "{{table}}_price"         ON "{{table}}" ("price") """,
        """ CREATE INDEX "{{table}}_displaced"     ON "{{table}}" ("displaced") """,
        """ CREATE INDEX "{{table}}_exported"      ON "{{table}}" ("exported") """,
        """ CREATE INDEX "{{table}}_expires"       ON "{{table}}" ("expires") """,
        """ CREATE INDEX "{{table}}_remains"       ON "{{table}}" ("remains") """,
        """ CREATE INDEX "{{table}}_minimal"       ON "{{table}}" ("minimal") """,
        """ CREATE INDEX "{{table}}_station_id"    ON "{{table}}" ("region_id") """,
        """ CREATE INDEX "{{table}}_system_id"     ON "{{table}}" ("system_id") """,
        """ CREATE INDEX "{{table}}_region_id"     ON "{{table}}" ("station_id") """,
        """ CREATE INDEX "{{table}}_item_id"       ON "{{table}}" ("item_id") """,
        """ CREATE INDEX "{{table}}_o_e_e_minimalprice" ON "{{table}}" ("displaced", "exported", "expires", ("minimal" * "price")) """,
        """ CREATE INDEX "{{table}}_minimalprice" ON "{{table}}" (("minimal" * "price")) """,

        # Index for calculating "displaced" field of newly inserted record (SQL_INSERT), and for selecting old record to "displace" them by new ones (SQL_DISPLACE).
        # NB: Order of fields is intuitive, since they give almost the same distribution of values. This may be changed in future to speed up inserting.
        # But compared for equality ("=") fields must be first; compared for range (">", "<", "<=", ">=") -- last.
        """ CREATE INDEX "{{table}}_i_r_e_d"       ON "{{table}}" ("item_id", "region_id", "exported", "displaced") """,
    ]

    # Mark old orders as overrided by incoming new bulk of orders in the same region and for the same item type.
    # Only orders preceding new export time are affected; orders going after new export time are never touched.
    # Condition on "displaced" field means: either not displaced orders at all (i.e. those being "latest ones");
    # or already displaced, but with bulk of records, which were exported after incoming new bulk (in time).
    SQL_DISPLACE = ["""
        UPDATE "{{table}}"
        SET "displaced" = %(exported)s
        WHERE "displaced" > %(exported)s
          AND "exported" < %(exported)s
          AND "region_id" = %(region_id)s
          AND "item_id" = %(item_id)s
    """]

    SQL_CALCULATE_DISPLACED = """
        select coalesce((
                select least((
                    select min("exported")
                    from "sellers"
                    where "exported" > %(exported)s
                      and "region_id" = %(region_id)s
                      and "item_id" = %(item_id)s 
                ),(
                    select min("exported")
                    from "buyers"
                    where "exported" > %(exported)s
                      and "region_id" = %(region_id)s
                      and "item_id" = %(item_id)s 
                ))
            ), 2000000000)
    """

    # In addition to just an insertion, this query calculates "displaced" field for inserting orders,
    # if there are other orders, exported earlier. I.e. if we insert not the latest orders.
    # These two queries (expire + insert) convert outdating timeline B1->B2 into B1->B(new)->B2.
    SQL_INSERT = ["""
        INSERT INTO "{{table}}" (
            "order_id",
            "station_id",
            "system_id",
            "region_id",
            "item_id",
            "range",
            "bid",
            "price",
            "remains",
            "entered",
            "minimal",
            "duration",
            "issued",
            "expires",
            "exported",
            "displaced",
            "export_item",
            "export_region"
        ) VALUES (
            %(order_id)s,
            %(station_id)s,
            %(system_id)s,
            %(region_id)s,
            %(item_id)s,
            %(range)s,
            %(bid)s,
            %(price)s,
            %(remains)s,
            %(entered)s,
            %(minimal)s,
            %(duration)s,
            %(issued)s,
            %(expires)s,
            %(exported)s,
            %(displaced)s,
            %(export_item)s,
            %(export_region)s
        )
    """]

    SQL_ANALYZE = ["""
        ANALYZE "{{table}}"
    """]


    def _subst(self, query, table):
        return query.replace("{{table}}", table)


    def drop(self):
        self.execute(self._subst(query, table) for table in self.TABLES for query in self.SQL_DROP)

    def create(self):
        self.execute(self._subst(query, table) for table in self.TABLES for query in self.SQL_CREATE)

    def insert(self, rows):
        rows = list(rows)
        if len(rows   ):
            displaced = self.query(self.SQL_CALCULATE_DISPLACED, {'item_id': rows[0]['item_id'], 'region_id': rows[0]['region_id'], 'exported': rows[0]['exported']}).fetchall()[0][0]
            for row in rows: row['displaced'] = displaced

        buyers  = list(filter(lambda row:     row['bid'], rows))
        sellers = list(filter(lambda row: not row['bid'], rows))
        if len(rows   ): self.execute((self._subst(query, table) for table in self.TABLES for query in self.SQL_DISPLACE), [{'item_id': rows[0]['item_id'], 'region_id': rows[0]['region_id'], 'exported': rows[0]['exported']}])
        if len(buyers ): self.execute((self._subst(query, self.TABLE_BUYERS )             for query in self.SQL_INSERT  ), buyers )
        if len(sellers): self.execute((self._subst(query, self.TABLE_SELLERS)             for query in self.SQL_INSERT  ), sellers)

    def analyze(self):
        self.execute(self._subst(query, table) for table in self.TABLES for query in self.SQL_ANALYZE)

    def adapt(self, fetcher):
        return\
            map(lambda row: {
                'order_id'          : row['orderID'],
                'station_id'        : row['stationID'],
                'system_id'         : row['solarSystemID'],
                'region_id'         : row['regionID'],
                'item_id'           : row['typeID'],
                'range'             : row['range'],
                'bid'               : row['bid'],
                'price'             : row['price'],
                'remains'           : row['volRemaining'],
                'entered'           : row['volEntered'],
                'minimal'           : row['minVolume'],
                'duration'          : 24*60*60*row['duration'],#(datetime.timedelta(row['duration'])),
                'issued'            : calendar.timegm(time.strptime(row['issued'], '%Y-%m-%d %H:%M:%S.%f')),   #(datetime.datetime.strptime(row['issued'], '%Y-%m-%d %H:%M:%S.%f')),#.strftime('%Y-%m-%d %H:%M:%S'),
                'expires'           : calendar.timegm(time.strptime(row['issued'], '%Y-%m-%d %H:%M:%S.%f')) + 24*60*60*row['duration'],#(datetime.datetime.strptime(row['issued'], '%Y-%m-%d %H:%M:%S.%f'))+datetime.timedelta(row['duration']),
                'exported'          : calendar.timegm(row['export_datetime'].timetuple()),
                'export_item'       : row['export_item'],
                'export_region'     : row['export_region'],
            },
            filter(lambda row: True,
            fetcher({
                'orderID'           : int,
                'stationID'         : int,
                'solarSystemID'     : int,
                'regionID'          : int,
                'typeID'            : int,
                'range'             : int,
                'bid'               : bool,
                'price'             : float,
                'volRemaining'      : float,
                'volEntered'        : float,
                'minVolume'         : float,
                'duration'          : int,# timedelta, days
                'issued'            : str,#datetime/timestamp
                'export_datetime'   : datetime.datetime,
                'export_item'       : str,
                'export_region'     : str,
            })))
